# /usr/bin/env python3.6
# -*- encoding: utf-8 -*-
"""
@File    : InsertCities.py
@Time    : 2020/2/6 0006 17:30
@Author  : zhangbing
@Email   : 18829172841@163.com
@Software: PyCharm
"""
import json

import pymysql


def load_data():
    with  open('./data/cities.json')  as  cities:
        cites_json_str = cities.read()
        cites_json = json.loads(cites_json_str)
        return cites_json


def insert_cities(cites_json):
    keys = cites_json.keys()
    db = pymysql.Connect(host="localhost", user="flask", port=3306, password="flask", database="flasktpp")
    cursor = db.cursor()
    for key in keys:
        cursor.execute("insert into  letter(letter) values('%s');" % key)
        db.commit()
        cursor.execute("select letter.id  from  letter  where  letter.letter='%s';" % key)
        letter_id = cursor.fetchone()[0]
        print(letter_id)

        '''
        letter_id = db.Column(db.Integer, db.ForeignKey(Letter.id))
        c_id = db.Column(db.Integer, default=0)
        c_parent_id = db.Column(db.Integer, default=0)
        c_region_name = db.Column(db.String(16))
        c_city_code = db.Column(db.Integer, default=0)
        c_pinyin = db.Column(db.String(64))
        '''
        cities_letter = cites_json.get(key)
        for city in cities_letter:
            c_id = city.get("id")
            c_parent_id = city.get("parentId")
            c_region_name = city.get("regionName")
            c_city_code = city.get("cityCode")
            c_pinyin = city.get("pinYin")
            print(letter_id)
            print(c_id, c_parent_id, c_region_name, c_city_code, c_pinyin)
            cursor.execute(
                "insert  into city(letter_id,c_id,c_parent_id,c_region_name,c_city_code,c_pinyin) values(%d,%d,%d,'%s',%d,'%s');" % (
                    letter_id, c_id, c_parent_id, c_region_name, c_city_code, c_pinyin))
            db.commit()
    db.close()


if __name__ == "__main__":
    data = load_data()
    insert_cities(data)
